import sequelize from "../../config/db.js";

/**
 * @description 检查角色菜单关系是否已存在
 * @param {Object} roleMenuData - 角色菜单关系数据
 * @returns {Promise<Array>} 返回现有关系或空数组
 */
const existingRelation = async (roleMenuData) => {
    const [existingRelation] = await sequelize.query(
        `SELECT *
         FROM sys_role_menu
         WHERE role_id = :role_id
           AND menu_id = :menu_id
           AND isDeleted = 1`,
        {
            replacements: {
                role_id: roleMenuData.role_id,
                menu_id: roleMenuData.menu_id
            }
        }
    );
    return existingRelation;
};

/**
 * @description 创建角色菜单关系
 * @param {Object} roleMenuData - 角色菜单关系数据
 * @returns {Promise<void>}
 */
const createRoleMenu = async (roleMenuData) => {
    await sequelize.query(
        `INSERT INTO sys_role_menu (role_id, menu_id, isDeleted)
         VALUES (:role_id, :menu_id, 1)`,
        {
            replacements: {
                role_id: roleMenuData.role_id,
                menu_id: roleMenuData.menu_id
            }
        }
    );
};

/**
 * @description 获取指定角色的菜单关系
 * @param {number} roleId - 角色 ID
 * @returns {Promise<Array|null>} 返回角色菜单关系列表或 null
 */
const getRoleMenuByRoleId = async (roleId) => {
    const [roleMenus] = await sequelize.query(
        `SELECT *
         FROM sys_role_menu
         WHERE role_id = :roleId
           AND isDeleted = 1`,
        {
            replacements: {roleId}
        }
    );
    return roleMenus.length ? roleMenus : null;
};

/**
 * @description 逻辑删除角色菜单关系
 * @param {number} roleMenuId - 角色菜单关系 ID
 * @returns {Promise<void>}
 */
const deleteRoleMenu = async (roleMenuId) => {
    await sequelize.query(
        `UPDATE sys_role_menu
         SET isDeleted = 0
         WHERE id = :roleMenuId`,
        {
            replacements: {roleMenuId}
        }
    );
};

/**
 * @description 获取指定用户的角色和菜单关联信息
 * @param {number} userId - 用户 ID，用于获取该用户的角色及其菜单权限信息
 * @returns {Promise<Array>} 返回与用户关联的角色和菜单信息列表
 */
const getAllUserRoleRoleMenu = async (userId) => {
    // 执行查询，返回关联的用户、角色和菜单信息
    const [results] = await sequelize.query(
        `
            SELECT su.id   AS user_id,
                   su.role_id,
                   sr.role_name,
                   su.username,
                   su.email,
                   su.phone,
                   su.name,
                   su.sex,
                   su.status,
                   su.bio,
                   su.registeredAt,
                   su.lastLoginAt,
                   su.registeredIP,
                   su.lastLoginIP,
                   sm.name AS menu_name,
                   sm.url,
                   sm.menu_type,
                   sm.description,
                   sm.style,
                   sm.isAccessible
            FROM sys_users AS su
                     JOIN sys_role AS sr ON sr.id = su.role_id
                     JOIN sys_role_menu AS srm ON sr.id = srm.role_id
                     JOIN sys_menu AS sm ON srm.menu_id = sm.id
            WHERE su.isDeleted = 1
              AND sr.isDeleted = 1
              AND srm.isDeleted = 1
              AND sm.isDeleted = 1
              AND su.id = :userId
        `, {
            replacements: {userId}
        }
    )
    return results;
};

export default {existingRelation, createRoleMenu, getRoleMenuByRoleId, deleteRoleMenu, getAllUserRoleRoleMenu};
